'''
    本文档使用PyQT实现对数据库的增删查改操作。
    主要功能包括：
        1、连接数据库 本例仅以SQLITE为例
        2、关闭连接数据库
        3、选择表
        4、表编辑功能：删除、创建
        5、表数据功能：增删查改
'''
import sqlite3

from PyQt5.QtCore import Qt, pyqtSignal
from PyQt5.QtGui import QIcon
from PyQt5.QtWidgets import (QWidget, QMainWindow, QMessageBox, QListView, QTableView,
                             QApplication, QAction, QInputDialog, QGridLayout, QLabel, QLineEdit,
                             QHBoxLayout, QPushButton, QVBoxLayout, QTableWidget, QTableWidgetItem,
                             QComboBox, QGroupBox, QCheckBox, QFileDialog, QListView, QTextEdit, QTableView,
                             QSplitter, QListWidget, QListWidgetItem)
from PyQt5.QtSql import QSqlDatabase, QSqlQueryModel, QSqlQuery, QSqlTableModel

import sys

class createTable(QWidget):
    sqlCreateTable = pyqtSignal(str)
    def __init__(self, parents=None):
        super(createTable, self).__init__(parents)
        self.initUI()
        self.show()

    def initUI(self):
        # 属性设置
        self.setWindowTitle("新建数据表")
        self.setGeometry(100, 100, 1500, 800)

        # 表格
        self.hbox = QHBoxLayout()
        tableNameLabel = QLabel("表名称")
        self.tableNameEdit = QLineEdit()
        self.addRowBtn = QPushButton("增加行", self)
        self.addRowBtn.clicked.connect(self.addRow)
        self.delRowBtn = QPushButton("删除行", self)
        self.delRowBtn.clicked.connect(self.delRow)
        self.hbox.addWidget(tableNameLabel)
        self.hbox.addWidget(self.tableNameEdit)
        self.hbox.addWidget(self.addRowBtn)
        self.hbox.addWidget(self.delRowBtn)


        self.vbox = QVBoxLayout(self)
        hTitle = ["列名", "数据类型", "长度", "默认", "主键？", "非空?", "Unsigned?", "自增?", "注释"]
        self.tbw = QTableWidget(self)
        self.tbw.setColumnCount(len(hTitle))
        self.tbw.setHorizontalHeaderLabels(hTitle)
        self.tbw.showGrid()
        self.tbw.setColumnWidth(0, 300)
        self.tbw.setColumnWidth(4, 100)
        self.tbw.setColumnWidth(5, 100)
        self.tbw.setColumnWidth(6, 100)
        self.tbw.setColumnWidth(7, 100)
        self.tbw.setColumnWidth(8, 300)

        # 按钮
        okBtn = QPushButton("确认")
        okBtn.clicked.connect(self.okBtnAction)
        cancelBtn = QPushButton("取消")
        cancelBtn.clicked.connect(self.closeAct)
        hbox2 = QHBoxLayout()
        hbox2.addWidget(okBtn)
        hbox2.addWidget(cancelBtn)

        self.vbox.addLayout(self.hbox)
        self.vbox.addWidget(self.tbw)
        self.vbox.addLayout(hbox2)

    def addRow(self):
        self.tbw.insertRow(self.tbw.rowCount())
        rowNumber = self.tbw.rowCount() - 1

        combox = QComboBox()
        combox.addItem("INTEGER")
        combox.addItem("REAL")
        combox.addItem("CHAR")
        combox.addItem("TEXT")
        combox.addItem("BLOB")

        self.tbw.setCellWidget(rowNumber, 1, combox)
        self.tbw.setItem(rowNumber, 2, QTableWidgetItem("20"))
        self.tbw.setItem(rowNumber, 3, QTableWidgetItem("NULL"))
        self.tbw.setCellWidget(rowNumber, 4, QCheckBox())
        self.tbw.setCellWidget(rowNumber, 5, QCheckBox())
        self.tbw.setCellWidget(rowNumber, 6, QCheckBox())
        self.tbw.setCellWidget(rowNumber, 7, QCheckBox())

    def delRow(self):
        index = self.tbw.currentIndex().row()
        if index >= 0 and index < self.tbw.rowCount():
            self.tbw.removeRow(index)

    def okBtnAction(self):

        tableName = self.tableNameEdit.text()
        if tableName == '':
            self.errorDisplay("表名称不能为空")
            return
        m = self.tbw.columnCount()
        n = self.tbw.rowCount()
        data = []
        for i in range(n):
            # 名称
            name = self.tbw.item(i, 0).text()
            if name == '':
                self.errorDisplay("列名不能为空")
            # 类型
            dataType = self.tbw.cellWidget(i, 1).currentText()
            # 大小
            dataSize = self.tbw.item(i, 2).text()
            # 默认
            defaultValue = self.tbw.item(i, 3).text()
            # 主键
            keyValue = self.tbw.cellWidget(i, 4).checkState()
            # 非空
            notNUll = self.tbw.cellWidget(i, 5).checkState()
            # 自增
            autoIncre = self.tbw.cellWidget(i, 7).checkState()
            # 描述
            comment = ''
            if self.tbw.item(i, 8) is not None:
                comment = self.tbw.item(i, 8).text()
            res = name
            if dataType == "CHAR":
                res += " %s(%s)"%(dataType, dataSize)
            else:
                res += " %s"%dataType
            if keyValue != 0:
                res += " PRIMARY KEY"
            if notNUll != 0:
                res += " NOT NULL"
            if defaultValue != "NULL":
                res += " DEFAULT('%s')"%defaultValue
            if autoIncre != 0:
                res += " AUTOINCREMENT"
            if comment != "":
                res += " COMMENT '%s' "%comment
            data.append(res)

        member = ""
        for i, res in enumerate(data):
            member += res
            if i != len(data)-1:
                member += ','
        reslut = "CREATE TABLE %s(%s);"%(tableName, member)
        self.sqlCreateTable[str].emit(reslut)
        self.close()

    def closeAct(self):
        self.close()

    def errorDisplay(self, s:str):
        QMessageBox.critical(self, "错误!", s)

class sqlTest(QMainWindow):
    def __init__(self):
        super(sqlTest, self).__init__()
        self.dbName = None
        self.db = None
        self.md = None
        self.con = None
        self.initUI()
        self.show()
        self.openDB()

    def initUI(self):
        # 设置窗口属性
        self.setGeometry(200, 200, 1080, 960)
        self.setWindowTitle("sqlTest")
        self.setWindowIcon(QIcon("imgs/snowman.jpg"))

        # 设置菜单
        self.menu = self.menuBar()
        self.dbMenu = self.menu.addMenu("数据库")
        # 打开数据库
        self.actOpenDB = QAction("打开数据库")
        self.actOpenDB.setIcon(QIcon("imgs/openDataBase.jpg"))
        self.actOpenDB.setToolTip("打开数据库")
        self.actOpenDB.triggered.connect(self.openDB)
        self.dbMenu.addAction(self.actOpenDB)
        # 关闭数据库
        self.actCloseDB = QAction("关闭数据库")
        self.actCloseDB.setIcon(QIcon("imgs/closeDataBase.jpg"))
        self.actCloseDB.setToolTip("关闭数据库")
        self.actCloseDB.triggered.connect(self.closeDB)
        self.dbMenu.addAction(self.actCloseDB)
        # 分割线
        self.dbMenu.addSeparator()
        # 新建表
        self.actNewTable = QAction("新建数据表")
        self.actNewTable.setIcon(QIcon("imgs/add.jpg"))
        self.actNewTable.setToolTip("新建数据表")
        self.actNewTable.triggered.connect(self.newTable)
        self.actNewTable.setToolTip("新建数据表")
        self.dbMenu.addAction(self.actNewTable)
        # 展示表
        self.showTable = QAction("展示表")
        self.showTable.setIcon(QIcon("imgs/refresh.jpg"))
        self.showTable.setToolTip("刷新数据表")
        self.showTable.triggered.connect(self.showTables)
        self.dbMenu.addAction(self.showTable)
        # 删除表
        self.delTable = QAction("删除表")
        self.delTable.setIcon(QIcon("imgs/del.jpg"))
        self.delTable.setToolTip("删除数据表")
        self.delTable.triggered.connect(self.delTables)
        self.dbMenu.addAction(self.delTable)

        ## 编辑
        self.edit = self.menu.addMenu("编辑")
        # 增加数据
        self.actAddData = QAction("增加数据")
        self.actAddData.setIcon(QIcon("imgs/addData.jpg"))
        self.actAddData.setToolTip("添加数据")
        self.actAddData.triggered.connect(self.addDataFunc)
        self.edit.addAction(self.actAddData)
        # 删除数据
        self.actDelData = QAction("删除数据")
        self.actDelData.setIcon(QIcon("imgs/delData.jpg"))
        self.actDelData.setToolTip("删除数据")
        self.actDelData.triggered.connect(self.delDataFunc)
        self.edit.addAction(self.actDelData)
        # 修改数据
        self.actEditData = QAction("修改数据")
        self.actEditData.setIcon(QIcon("imgs/editData.jpg"))
        self.actEditData.setToolTip("修改数据")
        self.edit.addAction(self.actEditData)
        # 查找数据
        self.actFindData = QAction("查找数据")
        self.actFindData.setIcon(QIcon("imgs/findData.jpg"))
        self.actFindData.setToolTip("查找数据")
        self.actFindData.triggered.connect(self.findDataFunc)
        self.edit.addAction(self.actFindData)
        # 增加分隔栏
        self.edit.addSeparator()
        # 执行SQL语句
        self.actExeSql = QAction("执行SQL语句")
        self.actExeSql.setIcon(QIcon("imgs/exec.jpg"))
        self.actExeSql.setToolTip("执行SQL命令")
        self.actExeSql.triggered.connect(self.exeSqlFunc)
        self.edit.addAction(self.actExeSql)
        # 清空SQL语句
        self.actClearSql = QAction("清空SQL语句")
        self.actClearSql.setIcon(QIcon("imgs/clear.jpg"))
        self.actClearSql.setToolTip("清空SQL命令")
        self.actClearSql.triggered.connect(self.clearSqlFunc)
        self.edit.addAction(self.actClearSql)

        # 查询框
        self.filt = QLineEdit(self)


        # 工具栏
        self.toolbar = self.addToolBar("eidt")
        self.toolbar.addAction(self.actOpenDB)
        self.toolbar.addAction(self.actCloseDB)
        self.toolbar.addSeparator()
        self.toolbar.addAction(self.actNewTable)
        self.toolbar.addAction(self.showTable)
        self.toolbar.addAction(self.delTable)
        self.toolbar.addSeparator()
        self.toolbar.addAction(self.actAddData)
        self.toolbar.addAction(self.actDelData)
        self.toolbar.addWidget(self.filt)
        self.toolbar.addAction(self.actFindData)
        self.toolbar.addAction(self.actEditData)
        self.toolbar.addSeparator()
        self.toolbar.addAction(self.actExeSql)
        self.toolbar.addAction(self.actClearSql)


        # 中央控件
        self.tableList = QListWidget(self) # 表栏
        self.tableList.itemDoubleClicked.connect(self.getData)
        self.dataTable = QTableView(self) # 数据显示栏
        self.sqlEdit = QTextEdit(self) # 原生SQL语句执行栏
        self.sqlEdit.setFontPointSize(12)




        # 中央控件布局
        self.sp1 = QSplitter(self)
        self.sp1.setOrientation(Qt.Vertical)
        self.sp1.addWidget(self.dataTable)
        self.sp1.addWidget(self.sqlEdit)
        self.sp2 = QSplitter(self)
        self.sp2.setOrientation(Qt.Horizontal)
        self.sp2.addWidget(self.tableList)
        self.sp2.addWidget(self.sp1)
        self.setCentralWidget(self.sp2)



        # 设置状态栏
        self.status = self.statusBar()
        self.status.showMessage("数据库未连接")
        self.status.showNormal()


    def openDB(self):
        res = QFileDialog.getSaveFileName(self, "打开数据库", "./", "SQLITE数据库 (*.db)")
        if res[0] is not None:
            self.dbName = res[0]
        self.db = QSqlDatabase("QSQLITE")
        self.db.setDatabaseName(self.dbName)  # 此处是set而不是Add
        if not self.db.open():
            QMessageBox.critical(self, "Error", "打开数据库失败")
            self.dbName = None
            self.status.showMessage("数据库连接失败")
        else:
            self.status.showMessage("数据库已连接")
            self.showTables()


    def closeDB(self):
        if self.db.isOpen():
            self.db.close()
        self.status.showMessage("数据库已关闭")

    def closeEvent(self, a0):
        self.closeDB()

    def newTable(self):
        self.newTableWidget = createTable()
        self.newTableWidget.sqlCreateTable[str].connect(self.createTable)


    def createTable(self, sql:str):
        try:
            if self.con is None:
                self.con = sqlite3.connect(self.dbName)
            cursor = self.con.cursor()
            cursor.execute(sql)
            QMessageBox().information(self, "成功!", "创建数据库成功!")
            self.showTables()
        except Exception as e:
            QMessageBox().critical(self, "错误!", str(e))


    def showTables(self):
        """
        展示当前数据库中所有的数据表，并展示在self.tableList中
        :return: None
        """
        try:
            if self.con is None:
                self.con = sqlite3.connect(self.dbName)
            cursor = self.con.cursor()
            cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
            ans = cursor.fetchall()
            self.tableList.clear()
            for a in ans:
                self.tableList.addItem(QListWidgetItem(a[0]))
        except Exception as e:
            QMessageBox().critical(self, "错误!", str(e))

    def delTables(self):
        """
        删除选定的表
        :return: None
        """
        curIndex = self.tableList.currentIndex()
        row = curIndex.row()
        data = curIndex.data()
        try:
            if self.con is None:
                self.con = sqlite3.connect(self.dbName)
            cursor = self.con.cursor()
            cursor.execute("DROP TABLE %s"%data)
            QMessageBox().information(self, "超过!", "成功删除表%s"%data)
            self.tableList.setRowHidden(row, True)
        except Exception as e:
            QMessageBox().critical(self, "错误!", str(e))

    def exeSqlFunc(self):
        sql = self.sqlEdit.document().toPlainText()
        try:
            self.md = QSqlTableModel(self, self.db)
            self.md.setQuery(QSqlQuery(sql, self.db))
            if self.md.lastError().text() != '':
                QMessageBox().critical(self, "错误!", str(self.md.lastError().text()))
                return
            self.dataTable.setModel(self.md)
        except Exception as e:
            QMessageBox().critical(self, "错误!", str(e))

    def getData(self):
        name = self.tableList.currentIndex().data()
        try:
            self.md = QSqlTableModel(self, self.db)
            self.md.setTable(name)
            self.md.select()
            self.md.setEditStrategy(QSqlTableModel.OnRowChange) # QSqlTableModel.OnFieldChange QSqlTableModel.OnManualSubmit
            if self.md.lastError().text() != '':
                QMessageBox().critical(self, "错误!", str(self.md.lastError().text()))
                return
            self.dataTable.setModel(self.md)

        except Exception as e:
            QMessageBox().critical(self, "错误!", str(e))

    def addDataFunc(self):
        if self.md is None:
            QMessageBox().information(self, "错误!", "请先选择数据表!")
        else:
            rowNumer = self.md.rowCount()
            self.md.insertRow(rowNumer)
            self.tableList.scrollToBottom()

    def delDataFunc(self):
        curIndex = self.tableList.currentIndex().row()
        self.md.removeRow(curIndex)
        self.dataTable.setRowHidden(curIndex, True)

    def clearSqlFunc(self):
        self.sqlEdit.clear()

    def findDataFunc(self):
        tableName = self.tableList.currentIndex().data()
        cond = self.filt.text()
        try:
            self.md.setTable(tableName)
            self.md.select()
            if self.md.lastError().text() != '':
                QMessageBox().critical(self, "错误!", str(self.md.lastError().text()))
                return
            self.md.setFilter(cond)
            if self.md.lastError().text() != '':
                QMessageBox().critical(self, "错误!", str(self.md.lastError().text()))
                return
            self.dataTable.setModel(self.md)
        except Exception as e:
            QMessageBox().critical(self, "错误!", str(e))


if __name__ == "__main__":
    app = QApplication(sys.argv)
    e = sqlTest()
    sys.exit(app.exec_())


